package cn.hd01.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import cn.hd01.repository.entity.Customer;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {
	
	@Query(value="SELECT c.ID, CONCAT(IF(d.shop_name IS NULL, '', CONCAT('(', d.shop_name, ')')), c.name) FROM customer c LEFT JOIN distributor_details d ON c.recommend_distributor_id = d.distributor_id WHERE c.type = '0'", nativeQuery=true)
	List<Object[]> getAllCustomer();
	
	Page<Customer> findAll(Specification<Customer> spec, Pageable pageable);

	Customer findByPhoneNo(String phoneNo);
	
	@Modifying
	@Transactional
	@Query(value = "update Customer set servant_id =?1, level_id =?2, name =?3, address =?4, province_id=?5, province=?6, cityId=?7, city=?8, areaId=?9, area=?10, recommendDistributorId=?11, update_time = now() where id=?12")
	int updateCustomer(Integer servantId, Integer levelId, String name, String address,
			Integer provinceId, String province, Integer cityId, String city, Integer areaId, String area, Integer recommendDistributorId, Integer id);

	@Modifying
	@Transactional
	@Query(value = "update Customer set status =?1, update_time = now() where id=?2")
	int updateCustomerStatus(String status, Integer id);

	@Modifying
	@Transactional
	@Query(value = "update Customer set audit_status =?1, update_time = now() where id=?2")
	int updateCustomerAuditStatus(String status, Integer id);

	@Modifying
	@Transactional
	@Query(value = "update Customer set servant_id = null, update_time = now() where servant_id = ?1 and type = ?2")
	int updateCustomerServant(Integer servantId, String type);
	
	@Modifying
	@Transactional
	@Query(value = "update Customer set recommend_distributor_id = null, update_time = now() where recommend_distributor_id = ?1")
	int updateCustomerDistributor(Integer distributorId);
	
	@Modifying
	@Transactional
	@Query(value = "update Customer set level_id=null where level_id=?1")
	int updateCustomerLevel(Integer id);

	Customer findByOauthId(Integer oauthId);

	Page<Customer> findByRecommendDistributorIdAndType(Integer recommendDistributorId, String type, Pageable pageable);
	
	@Query(value = "select id from Customer where servant_id = ?1 and type = ?2")
	List<Integer> findDistributorIdByServantId(Integer servantId, String type);
	
	int countByTypeAndAuditStatus(String type, String auditStatus);
	
	int countByTypeAndAuditStatusAndServantId(String type, String auditStatus, Integer servantId);
	
	@Query(value = "select id,name,recommendDistributorId from Customer where  type = ?1")
	List<Customer> findCustomerList(String type);
	
	@Query(value = "select id,name from Customer where  type = ?1 and audit_status=?2")
	List<Customer> findCustomerList(String type,String auditStatus);
	
	@Query(value = "select id,name from Customer where  type = ?1 and servant_id=?2")
	List<Customer> findCustomerList(String type,int servantId);
	
	/**
	 * 查询渠道商所属的客户数量
	 * @param distributorId
	 * @return
	 */
	@Query(value = "SELECT COUNT(1) FROM Customer WHERE recommend_distributor_id = ?1 and type = 0")
	Integer countCustomer(Integer distributorId);
	
	@Query(value = "select level, count(1) FROM Customer where type = 0 group by level")
	List<Object[]> findLevelCount();
}
